package com.ruoyi.system.service.impl;

import com.ruoyi.common.core.annotation.Excel;
import com.ruoyi.common.core.utils.CryptoUtils;
import com.ruoyi.common.core.utils.Global;
import com.ruoyi.common.core.utils.StringUtils;
import com.ruoyi.common.security.utils.SecurityUtils;
import com.ruoyi.system.api.domain.SysDept;
import com.ruoyi.system.api.domain.SysUser;
import com.ruoyi.system.domain.SysDict;
import com.ruoyi.system.domain.excle.QuestionNumDTO;
import com.ruoyi.system.mapper.SysDeptMapper;
import com.ruoyi.system.mapper.SysDictMapper;
import com.ruoyi.system.mapper.uploaddownload.UploadMapper;
import com.ruoyi.system.service.DownloadQuestionService;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Predicate;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
public class DownloadQuestionServiceImpl  implements DownloadQuestionService {

    @Autowired
    private SysDictMapper dictMapper;
    @Autowired
    private SysDeptMapper deptMapper;
    @Autowired
    private UploadMapper downloadMapper;

    private static final Logger log = LoggerFactory.getLogger(DownloadQuestionService.class);

    // HTML标签正则表达式
    private static final String REGEX_HTML = "<[^>]+>";
    private static final Pattern P_HTML = Pattern.compile(REGEX_HTML, Pattern.CASE_INSENSITIVE);

    // 固定表头定义
    private static final String[] HEAD0 = {"身份证号", "姓名", "性别", "出生日期", "年龄", "城乡工作种类", "所属机构", "机构代码", "问卷创建日期"};
    private static final String[] HEAD1 = {"工作单位", "民族", "职称", "职业", "行业", "文化程度", "家庭住址", "手机号"};

    @Override
    public void downloadExcel(HttpServletRequest request, HttpServletResponse response,
                              String organizationCode, String starttime, String endtime) {
        Workbook workbook = null;
        try {
            // 用户没选择地区，查询用户所属机构的所有数据
            if (StringUtils.isEmpty(organizationCode)) {
                SysUser user = SecurityUtils.getLoginUser().getSysUser();
                SysDept org = deptMapper.selectDeptById(user.getDeptId());
                organizationCode = org.getOrganizationcode();
            }

            // 根据日期查询数据表标识符
            SysDict dictYear = dictMapper.selectDictByLabel(starttime.substring(0, 4));

            // 设置响应头
            String fileName = URLEncoder.encode("问卷数据", "UTF-8") + ".xlsx";
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

            // 创建Excel工作簿
            workbook = new XSSFWorkbook();

            if (null != dictYear) {
                String huiyuanxinxiTableName = Global.DATATABLEPRE + "_" + dictYear.getValue() + Global.TESTWHOLEDATANAME;

                // 查询启用的问卷
                List<Map<String, Object>> questionList = downloadMapper.getQuestion("question", "1", null, null);

                for (Map<String, Object> map : questionList) {
                    String questionId = map.get("questionId").toString();
                    String questionTitle = (String) map.get("questionTitle");

                    // 根据问卷id查询该问卷包含的问题
                    List<Map<String, Object>> paperList = downloadMapper.getPaper(questionId);
                    List<String> paperIdStringList = new ArrayList<>();
                    for (Map<String, Object> paper : paperList) {
                        paperIdStringList.add(paper.get("paperId").toString());
                    }

                    // 查询问卷数据
                    Map<String, Object> paramMap = new HashMap<>();
                    paramMap.put("questionId", questionId);
                    paramMap.put("paperIdStringList", paperIdStringList);
                    paramMap.put("organizationCode", organizationCode);
                    paramMap.put("huiyuanxinxiTableName", huiyuanxinxiTableName);
                    paramMap.put("starttime", starttime);
                    paramMap.put("endtime", endtime);

                    List<Map<String, Object>> questionDataList = downloadMapper.getDownloadQuestionData(paramMap);

                    // 转换为DTO列表
                    List<QuestionNumDTO> dtoList = convertToQuestionnaireDTO(questionDataList, paperList);

                    // 为当前问卷创建工作表
                    createSheetForQuestion(workbook, dtoList, paperList, questionTitle);
                }
            } else {
                // 没有数据表，创建提示工作表
                createNoDataSheet(workbook, starttime);
            }

            // 写入响应流
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("导出问卷数据失败", e);
            throw new RuntimeException("导出问卷数据失败", e);
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.error("关闭工作簿失败", e);
                }
            }
        }
    }

    // 为问卷创建工作表
    private void createSheetForQuestion(Workbook workbook, List<QuestionNumDTO> dtoList,
                                        List<Map<String, Object>> paperList, String sheetName) {
        if (dtoList.isEmpty()) {
            return;
        }

        // 创建工作表，处理名称长度和非法字符
        String safeSheetName = sanitizeSheetName(sheetName);
        Sheet sheet = workbook.createSheet(safeSheetName);

        // 创建表头
        Row headerRow = sheet.createRow(0);
        int colIndex = 0;

        // 添加基本字段表头
        List<Field> baseFields = getAnnotatedFields(QuestionNumDTO.class,
                field -> !field.getName().equals("questionFields"));

        for (Field field : baseFields) {
            Excel annotation = field.getAnnotation(Excel.class);
            Cell cell = headerRow.createCell(colIndex++);
            cell.setCellValue(annotation.name());

            // 设置表头样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerStyle.setFont(headerFont);
            cell.setCellStyle(headerStyle);
        }

        // 添加动态问题字段表头
        if (!dtoList.isEmpty()) {
            QuestionNumDTO sample = dtoList.get(0);
            for (String fieldName : sample.getQuestionFieldNames()) {
                Cell cell = headerRow.createCell(colIndex++);
                cell.setCellValue(fieldName);

                // 设置表头样式
                CellStyle headerStyle = workbook.createCellStyle();
                Font headerFont = workbook.createFont();
                headerFont.setBold(true);
                headerStyle.setFont(headerFont);
                cell.setCellStyle(headerStyle);
            }
        }

        // 填充数据行
        int rowIndex = 1;
        for (QuestionNumDTO dto : dtoList) {
            Row dataRow = sheet.createRow(rowIndex++);
            colIndex = 0;

            // 填充基本字段
            for (Field field : baseFields) {
                try {
                    field.setAccessible(true);
                    Object value = field.get(dto);
                    dataRow.createCell(colIndex++).setCellValue(value != null ? value.toString() : "");
                } catch (IllegalAccessException e) {
                    log.warn("设置字段{}失败", field.getName(), e);
                    dataRow.createCell(colIndex++).setCellValue("");
                }
            }

            // 填充动态问题字段
            if (!dtoList.isEmpty()) {
                for (String fieldName : dtoList.get(0).getQuestionFieldNames()) {
                    String value = dto.getQuestionField(fieldName);
                    dataRow.createCell(colIndex++).setCellValue(value != null ? value : "");
                }
            }
        }

        // 自动调整列宽
        for (int i = 0; i < colIndex; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    // 创建无数据提示工作表
    private void createNoDataSheet(Workbook workbook, String year) {
        Sheet sheet = workbook.createSheet("提示");
        Row headerRow = sheet.createRow(0);
        Cell headerCell = headerRow.createCell(0);
        headerCell.setCellValue("提示信息");

        // 设置表头样式
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerCell.setCellStyle(headerStyle);

        Row dataRow = sheet.createRow(1);
        dataRow.createCell(0).setCellValue(year + "年度尚未创建数据表，无法导出数据");

        // 调整列宽
        sheet.autoSizeColumn(0);
    }
    // 数据转换方法
    private List<QuestionNumDTO> convertToQuestionnaireDTO(List<Map<String, Object>> questionDataList,
                                                           List<Map<String, Object>> paperList) {
        List<QuestionNumDTO> result = new ArrayList<>();

        // 首先确定所有动态字段名称
        Map<String, String> fieldNameMap = generateFieldNames(paperList);

        for (Map<String, Object> objectMap : questionDataList) {
            QuestionNumDTO dto = new QuestionNumDTO();

            try {
                // 设置基本字段
                dto.setIdcard(CryptoUtils.decryptSymmetrically(Global.COLS_AES_SECRETKEY, null,
                        getStringValue(objectMap, "idcard"), CryptoUtils.Algorithm.Encryption.AES_ECB_PKCS5));
                dto.setRealname(CryptoUtils.decryptSymmetrically(Global.COLS_AES_SECRETKEY, null,
                        getStringValue(objectMap, "realname"), CryptoUtils.Algorithm.Encryption.AES_ECB_PKCS5));
                dto.setSex(getStringValue(objectMap, "sex"));
                dto.setBirthdate(getStringValue(objectMap, "birthdate"));
                dto.setAge(getStringValue(objectMap, "age"));
                dto.setTowncountry(getStringValue(objectMap, "towncountry"));
                dto.setMechanismname(getStringValue(objectMap, "mechanismname"));
                dto.setMechanismcode(getStringValue(objectMap, "mechanismcode"));

                String createTime = getStringValue(objectMap, "createTime");
                if (StringUtils.isNotEmpty(createTime) && createTime.length() >= 10) {
                    dto.setCreateTime(createTime.substring(0, 10));
                }

                // 设置个人信息字段
                dto.setWorkunit(CryptoUtils.decryptSymmetrically(Global.COLS_AES_SECRETKEY, null,
                        getStringValue(objectMap, "workunit"), CryptoUtils.Algorithm.Encryption.AES_ECB_PKCS5));
                dto.setNation(getStringValue(objectMap, "nation"));
                dto.setPosition(getStringValue(objectMap, "position"));
                dto.setOccupation(getStringValue(objectMap, "occupation"));
                dto.setIndustry(getStringValue(objectMap, "industry"));
                dto.setCulture(getStringValue(objectMap, "culture"));
                dto.setAddress(CryptoUtils.decryptSymmetrically(Global.COLS_AES_SECRETKEY, null,
                        getStringValue(objectMap, "address"), CryptoUtils.Algorithm.Encryption.AES_ECB_PKCS5));
                dto.setPhone(CryptoUtils.decryptSymmetrically(Global.COLS_AES_SECRETKEY, null,
                        getStringValue(objectMap, "phone"), CryptoUtils.Algorithm.Encryption.AES_ECB_PKCS5));

                // 设置问卷问题字段
                setQuestionnaireFields(dto, objectMap, paperList, fieldNameMap);

            } catch (Exception e) {
                log.warn("设置字段失败", e);
            }

            result.add(dto);
        }

        return result;
    }

    // 生成动态字段名称映射
    private Map<String, String> generateFieldNames(List<Map<String, Object>> paperList) {
        Map<String, String> fieldNameMap = new LinkedHashMap<>();
        int fieldCounter = 1;

        for (Map<String, Object> paper : paperList) {
            String paperId = getStringValue(paper, "paperId");
            String paperTitle = getStringValue(paper, "paperTitle");
            int n = calculateQuestionCount(paper);

            if (n == 0) {
                // 单字段问题
                fieldNameMap.put(paperId, "问题" + fieldCounter++ + ": " + paperTitle);
            } else {
                // 多字段问题
                for (int i = 1; i <= n; i++) {
                    fieldNameMap.put(paperId + "_" + i, "问题" + fieldCounter++ + "_" + i + ": " + paperTitle);
                }
            }
        }

        return fieldNameMap;
    }

    // 设置问卷问题字段
    private void setQuestionnaireFields(QuestionNumDTO dto, Map<String, Object> data,
                                        List<Map<String, Object>> paperList, Map<String, String> fieldNameMap) {
        for (Map<String, Object> paper : paperList) {
            String paperId = getStringValue(paper, "paperId");
            int n = calculateQuestionCount(paper);

            if (n == 0) {
                // 单字段问题
                String value = getStringValue(data, paperId);
                String fieldName = fieldNameMap.get(paperId);
                dto.addQuestionField(fieldName, value);
            } else {
                // 多字段问题
                String valueStr = getStringValue(data, paperId);
                String[] values = valueStr.split(",");

                for (int i = 0; i < n; i++) {
                    String value = (i < values.length) ? values[i].replaceAll("@", "\" \"") : "";
                    String fieldName = fieldNameMap.get(paperId + "_" + (i + 1));
                    dto.addQuestionField(fieldName, value);
                }
            }
        }
    }

    // 辅助方法：获取带有指定注解的字段
    private List<Field> getAnnotatedFields(Class<?> clazz, Predicate<Field> filter) {
        return Arrays.stream(clazz.getDeclaredFields())
                .filter(field -> field.isAnnotationPresent(Excel.class))
                .filter(filter)
                .sorted((f1, f2) -> {
                    Excel e1 = f1.getAnnotation(Excel.class);
                    Excel e2 = f2.getAnnotation(Excel.class);
                    return Integer.compare(e1.sort(), e2.sort());
                })
                .collect(Collectors.toList());
    }

    // 清理工作表名称（Excel工作表名称有长度和字符限制）
    private String sanitizeSheetName(String name) {
        if (name == null) {
            return "Sheet";
        }

        // 移除非法字符
        String safeName = name.replaceAll("[\\\\/*\\[\\]:?]", "");

        // 截断超过31个字符的名称
        if (safeName.length() > 31) {
            safeName = safeName.substring(0, 31);
        }

        // 确保名称不为空
        if (safeName.trim().isEmpty()) {
            return "Sheet";
        }

        return safeName;
    }

    // 计算问题数量
    private int calculateQuestionCount(Map<String, Object> paper) {
        int n = 0;
        String paperTitle = getStringValue(paper, "paperTitle");
        String choiceText = getStringValue(paper, "choice_text");
        String paperCss = getStringValue(paper, "paperCss");

        // 填空题题目中包含@
        if (paperTitle.contains("@")) {
            n = paperTitle.length() - paperTitle.replaceAll("@", "").length();
        }

        // 选择填空题选项中包含@
        if (StringUtils.isNotEmpty(choiceText) && choiceText.contains("@")) {
            n = choiceText.length() - choiceText.replaceAll("@", "").length() + 1;

            // 成年人11那个题型中，是填空题，但是题目中不包含@，在选项中包含@，所以列不需要加1
            if (StringUtils.isNotEmpty(paperCss) && "100".equals(paperCss)) {
                n = n - 1;
            }
        }

        return n;
    }

    // 获取字符串值
    private String getStringValue(Map<String, Object> data, String key) {
        Object value = data.get(key);
        return value == null ? "" : value.toString();
    }

}
